package com.union.core.sys.dao.impl;

import java.util.*;
import java.sql.ResultSet;
import org.apache.log4j.Logger;
import com.hnjowe.webdb.*;
import com.union.core.sys.dao.SysManagerDao;
import com.union.core.sys.model.*;

/**
 * 帐号管理DAO实现类
 * filename: SysManagerDaoImpl.java
 * project:  CCTR
 * @company Hainan Jowe Technology Ltd
 * @author wangxiuwu
 * @create in 2009-5-21
 * @version 1.0
 */
public class SysManagerDaoImpl implements SysManagerDao{
	private static Logger log = Logger.getLogger(SysManagerDaoImpl.class.getName());
	
	private DBOperation opera;
	private SysManager sysManager;
	
	public SysManagerDaoImpl() {
	}

	//#登陆帐号管理#////////////////////////////////////////////////////////////
	/**
	 * 1 读取登陆帐号
	 * @return List<UserBean>
	 */
	public List<SysManager> getManagerList() {
		List<SysManager> managerList = new ArrayList<SysManager>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();

			String sql = "select "
					+ "MANAGER_ID,MANAGER_NAME,MANAGER_PWD,MANAGER_STATUS,MODULE_MGR_PURVIEW,"
					+ "MANAGER_DESC,LOGIN_TIMES,LAST_LOGIN_IP,LAST_LOGIN_TIME,manager_weight,manager_roles,personnel_id"
					+ " from sys_manager order by MANAGER_ID";
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				sysManager = new SysManager();
				sysManager.setMANAGER_ID(res.getInt(1));
				sysManager.setMANAGER_NAME(res.getString(2));
				sysManager.setMANAGER_PWD(res.getString(3));
				sysManager.setMANAGER_STATUS(res.getInt(4));
				sysManager.setMODULE_MGR_PURVIEW(res.getString(5));
				sysManager.setMANAGER_DESC(res.getString(6));
				sysManager.setLOGIN_TIMES(res.getInt(7));
				sysManager.setLAST_LOGIN_IP(res.getString(8));
				sysManager.setLAST_LOGIN_TIME(res.getString(9));
				sysManager.setMANAGER_WEIGHT(res.getInt(10));
				sysManager.setMANAGER_ROLES(res.getString(11));
				sysManager.setPERSONNEL_ID(res.getInt(12));
				managerList.add(i, sysManager);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return managerList;
	}
	
	public SysManager getManager(int managerId) {
		SysManager sysManager = null;
		try {
			opera = new DBOperation();
			opera.transcationBegin();

			String sql = "select "
					+ "MANAGER_ID,MANAGER_NAME,MANAGER_PWD,MANAGER_STATUS,MODULE_MGR_PURVIEW,"
					+ "MANAGER_DESC,LOGIN_TIMES,LAST_LOGIN_IP,LAST_LOGIN_TIME,manager_weight,manager_roles,personnel_id"
					+ " from sys_manager where  MANAGER_ID=?";
			opera.prepStatment(sql);
			opera.setInt(1, managerId);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				sysManager = new SysManager();
				sysManager.setMANAGER_ID(res.getInt(1));
				sysManager.setMANAGER_NAME(res.getString(2));
				sysManager.setMANAGER_PWD(res.getString(3));
				sysManager.setMANAGER_STATUS(res.getInt(4));
				sysManager.setMODULE_MGR_PURVIEW(res.getString(5));
				sysManager.setMANAGER_DESC(res.getString(6));
				sysManager.setLOGIN_TIMES(res.getInt(7));
				sysManager.setLAST_LOGIN_IP(res.getString(8));
				sysManager.setLAST_LOGIN_TIME(res.getString(9));
				sysManager.setMANAGER_WEIGHT(res.getInt(10));
				sysManager.setMANAGER_ROLES(res.getString(11));
				sysManager.setPERSONNEL_ID(res.getInt(12));
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return sysManager;
	}

	/**
	 * 2 添加登陆帐号
	 * @param managerName String
	 * @param managerPwd String
	 * @param moduleMgrPurview String
	 * @param managerDesc String
	 * @return boolean
	 */
	public boolean addManager(SysManager sysManager) {
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "insert into sys_manager"
					+ "(MANAGER_NAME,MANAGER_PWD,MANAGER_STATUS,MODULE_MGR_PURVIEW,MANAGER_DESC,"
					+ "LOGIN_TIMES,LAST_LOGIN_IP,LAST_LOGIN_TIME,manager_weight,personnel_id) "
					+ "values(?,PASSWORD(?),1,?,?,0,'',SYSDATE(),?,?)";
			opera.prepStatment(sql);
			opera.setString(1, sysManager.getMANAGER_NAME());
			opera.setString(2, sysManager.getMANAGER_PWD());
			opera.setString(3, sysManager.getMODULE_MGR_PURVIEW());
			opera.setString(4, sysManager.getMANAGER_DESC());
			opera.setInt(5, sysManager.getMANAGER_WEIGHT());
			opera.setInt(6, sysManager.getPERSONNEL_ID());
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	/**
	 * 3.1 修改登陆帐号
	 * @param userBean UserBean
	 * @return boolean
	 */
	public boolean modifyManager(SysManager sysManager) {
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "update sys_manager "
					+ "set MANAGER_NAME=?,MODULE_MGR_PURVIEW=?,MANAGER_DESC=?,manager_roles=?"
					+ " where MANAGER_ID=?";
			opera.prepStatment(sql);
			opera.setString(1, sysManager.getMANAGER_NAME());
			opera.setString(2, sysManager.getMODULE_MGR_PURVIEW());
			opera.setString(3, sysManager.getMANAGER_DESC());
			opera.setString(4, sysManager.getMANAGER_ROLES());
			opera.setInt(5, sysManager.getMANAGER_ID());
			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	/**
	 * 3.2 修改登陆帐号状态
	 * @param managerId int
	 * @param state int
	 * @return boolean
	 */
	public boolean modifyManager(int managerId, int state) {
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();

			String sql = "update sys_manager " + "set MANAGER_STATUS=? "
					+ "where MANAGER_ID=?";
			opera.prepStatment(sql);
			opera.setInt(1, state);
			opera.setInt(2, managerId);
			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	/**
	 * 3.3 修改登陆帐号相关信息
	 * @param managerId int
	 * @param loginTimes int
	 * @param lastLoginIp String
	 * @return boolean
	 */
	public boolean modifyManager(int managerId, int loginTimes,String lastLoginIp) {
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();

			String sql = "update sys_manager "
					+ "set LOGIN_TIMES=?,LAST_LOGIN_IP=?,LAST_LOGIN_TIME=SYSDATE() "
					+ "where MANAGER_ID=?";
			opera.prepStatment(sql);
			opera.setInt(1, loginTimes);
			opera.setString(2, lastLoginIp);
			opera.setInt(3, managerId);
			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}
	/**
	 * 修改登陆账号密码
	 */
	public boolean modifyuserManager(int MANAGER_ID,String MANAGER_NAME,String MANAGER_PWD){
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "update sys_manager set MANAGER_NAME=?, MANAGER_PWD=PASSWORD(?) "
						 + "where MANAGER_ID=? ";
			opera.prepStatment(sql);	
			opera.setString(1, MANAGER_NAME);
			opera.setString(2, MANAGER_PWD);
			opera.setInt(3, MANAGER_ID);
			opera.executeUpdate();
			result = opera.transcationCommit();
		}catch (Exception e){
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		 return result;
	}
	/**
	 * 3.4 修改密码
	 * @param MANAGER_ID int
	 * @param MANAGER_NAME String
	 * @param MANAGER_PWD_NEW String
	 * @return boolean
	 */
	public boolean modifyPasswd(int MANAGER_ID,String MANAGER_PWD_NEW,String MANAGER_NAME ){
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "update sys_manager " + "set MANAGER_PWD=PASSWORD(?) " + "set MANAGER_NAME=?" 
					+ "where MANAGER_ID=? ";
			opera.prepStatment(sql);
			opera.setString(1, MANAGER_PWD_NEW);
			opera.setString(2, sysManager.getMANAGER_NAME());
			opera.setInt(3, MANAGER_ID);
			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	/**
	 * 4 登陆帐号删除
	 * @param managerId int
	 * @return boolean
	 */
	public boolean deleteManager(int managerId) {
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from sys_manager where MANAGER_ID=?";
			opera.prepStatment(sql);
			opera.setInt(1, managerId);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	/**
	 * 获取权限信息
	 * @param MANAGER_ROLES String
	 * @return List<String>
	 */
	public List<String> getModuleMgrPurviewList(String MANAGER_ROLES){
		List<String> moduleIdList = new ArrayList<String>();
		try {
			String moduleId = "";
			if (MANAGER_ROLES == null)
				MANAGER_ROLES = "";
			StringTokenizer st = new StringTokenizer(MANAGER_ROLES, "|");
			while (st.hasMoreTokens()) {
				moduleId = st.nextToken();
				if (moduleId != null)
					moduleIdList.add(moduleId);
			}
		} catch (Exception e) {
			log.error(e);
		}
		return moduleIdList;
	}

	/**
	 * 校验某帐号是否有某模块登陆权限
	 * @param ManagerName String
	 * @param ManagerPwd String
	 * @param moduleId int
	 * @return boolean
	 */
	public boolean ifHasModuleMgrPurview(String ManagerName, String ManagerPwd,int moduleId){
		boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select * "
					+ "from sys_manager "
					+ "where BINARY MANAGER_NAME=? and BINARY MANAGER_PWD=PASSWORD(?) and BINARY MODULE_MGR_PURVIEW like '%|"
					+ moduleId + "|%'";
			opera.prepStatment(sql);
			opera.setString(1, ManagerName);
			opera.setString(2, ManagerPwd);
			opera.setString(3, String.valueOf(moduleId));
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				result = true;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	/**
	 * 管理登陆认证
	 * @param managerName String
	 * @param managerPwd String
	 * @return UserBean
	 */
	public SysManager login(String managerName, String managerPwd){
		SysManager sysManager = null;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select "
					+ "MANAGER_ID,MANAGER_NAME,MANAGER_PWD,MANAGER_STATUS,MODULE_MGR_PURVIEW,"
					+ "MANAGER_DESC,LOGIN_TIMES,LAST_LOGIN_IP,LAST_LOGIN_TIME,manager_weight,manager_roles,personnel_id"
					+ " from sys_manager "
					+ "where binary MANAGER_NAME=? and binary MANAGER_PWD=PASSWORD(?) and MANAGER_STATUS=1";
			opera.prepStatment(sql);
			opera.setString(1, managerName);
			opera.setString(2, managerPwd);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				sysManager = new SysManager();
				sysManager.setMANAGER_ID(res.getInt(1));
				sysManager.setMANAGER_NAME(res.getString(2));
				sysManager.setMANAGER_PWD(res.getString(3));
				sysManager.setMANAGER_STATUS(res.getInt(4));
				sysManager.setMODULE_MGR_PURVIEW(res.getString(5));
				sysManager.setMANAGER_DESC(res.getString(6));
				sysManager.setLOGIN_TIMES(res.getInt(7));
				sysManager.setLAST_LOGIN_IP(res.getString(8));
				sysManager.setLAST_LOGIN_TIME(res.getString(9));
				sysManager.setMANAGER_WEIGHT(res.getInt(10));
				sysManager.setMANAGER_ROLES(res.getString(11));
				sysManager.setPERSONNEL_ID(res.getInt(12));
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return sysManager;
	}
	/**
	 * 添加后返回id
	 * return：id
	 * */
	public int getManagerId(String name) {
		int id=0;
		try {
			opera = new DBOperation();
			opera.transcationBegin();

			String sql = "select "
					+ "MANAGER_ID from sys_manager where  MANAGER_NAME=?";
			opera.prepStatment(sql);
			opera.setString(1, name);
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				id=res.getInt(1);
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return id;
	}
	/**
	 * 校验登陆用户帐号是否已存在
	 * @param loginName String
	 * @return boolean
	 * false-无记录 true-有记录
	 */
	public boolean checkLoginName(String loginName){
		boolean result = false;
	    try{
	      try{
	        opera = new DBOperation();
	        opera.transcationBegin();
	        String sql = "select * "
	                   + "from sys_manager "
	                   + "where MANAGER_NAME=?";
	        opera.prepStatment(sql);
	        opera.setString(1,loginName);
	        ResultSet res = opera.executeQuery();
	        if(res.next()){
	          result = true;
	        }
	      }catch(Exception ex){
	        result = false;
	      }
	    }catch(Exception e){
	      e.printStackTrace();
	    }finally{
	      opera.closePres();
	      opera.closeConn();
	    }
	    return result;
	}
	/**
	 * 1 返回拥有被删角色的账号
	 * @return List<UserBean>
	 */
	public List<SysManager> getManagerListByRoleId(String roleId) {
		List<SysManager> managerList = new ArrayList<SysManager>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();

			String sql = "select "
					+ "MANAGER_ID,MANAGER_NAME,MANAGER_PWD,MANAGER_STATUS,MODULE_MGR_PURVIEW,"
					+ "MANAGER_DESC,LOGIN_TIMES,LAST_LOGIN_IP,LAST_LOGIN_TIME,manager_weight,manager_roles,personnel_id"
					+ " from sys_manager where manager_roles like '%"+roleId+"'%";
			opera.prepStatment(sql);
			ResultSet res = opera.executeQuery();
			int i = 0;
			while (res.next()) {
				sysManager = new SysManager();
				sysManager.setMANAGER_ID(res.getInt(1));
				sysManager.setMANAGER_NAME(res.getString(2));
				sysManager.setMANAGER_PWD(res.getString(3));
				sysManager.setMANAGER_STATUS(res.getInt(4));
				sysManager.setMODULE_MGR_PURVIEW(res.getString(5));
				sysManager.setMANAGER_DESC(res.getString(6));
				sysManager.setLOGIN_TIMES(res.getInt(7));
				sysManager.setLAST_LOGIN_IP(res.getString(8));
				sysManager.setLAST_LOGIN_TIME(res.getString(9));
				sysManager.setMANAGER_WEIGHT(res.getInt(10));
				sysManager.setMANAGER_ROLES(res.getString(11));
				sysManager.setPERSONNEL_ID(res.getInt(12));
				managerList.add(i, sysManager);
				i++;
			}
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return managerList;
	}
}